﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace XT.Exam.ADO.Net
{
    public class DBHelper
    {
        /// <summary>
        /// 连接字符串从配置文件中读取
        /// </summary>
        private readonly static string CONNSTRING = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;

        /// <summary>
        ///  执行增删改操作方法
        /// </summary>
        /// <param name="sqlstr">SQL语句 【非Select 语句】</param>
        public static bool GetExecuteNonQuery(string sqlstr)
        {
            using (SqlConnection conn = new SqlConnection(CONNSTRING))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(sqlstr, conn);
                    conn.Open();
                    return cmd.ExecuteNonQuery() > 0;
                }
                catch (SqlException e)
                {
                    throw new Exception(e.Message);
                }
            }

        }

        /// <summary>
        ///  执行增删改操作方法
        /// </summary>
        /// <param name="sqlstr">SQL语句 【非Select 语句】 多条</param>
        public static bool GetExecuteNonQuery(List<string> sqlstr)
        {
            using (SqlConnection conn = new SqlConnection(CONNSTRING))
            {
                SqlTransaction tran = conn.BeginTransaction();
                try
                {
                    conn.Open();
                    int rowi = 0;
                    foreach (string sql in sqlstr)
                    {
                        SqlCommand cmd = new SqlCommand(sql, conn, tran);
                        rowi += cmd.ExecuteNonQuery();
                    }

                    if (rowi >= sqlstr.Count)
                    {
                        tran.Commit(); //提交
                        return true;
                    }
                    else
                    {
                        tran.Rollback();//回滚
                        return false;
                    }
                }
                catch (SqlException e)
                {
                    tran.Rollback(); //回滚
                    throw new Exception(e.Message);
                }
            }

        }

        /// <summary>
        ///  执行增删改操作方法
        /// </summary>
        /// <param name="sqlstr">SQL语句 【非Select 语句】</param>
        /// <param name="paras">参数</param>
        public static bool GetExecuteNonQuery(string sqlstr, params SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(CONNSTRING))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(sqlstr, conn);
                    cmd.Parameters.AddRange(paras);
                    conn.Open();
                    return cmd.ExecuteNonQuery() > 0;
                }
                catch (SqlException e)
                {

                    throw new Exception(e.Message);
                }
            }
        }


        /// <summary>
        /// 执行检索查询方法
        /// </summary>
        /// <param name="sqlstr">SQL语句【Select 语句】</param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sqlstr, params  SqlParameter[] paras)
        {
            try
            {
                SqlConnection conn = new SqlConnection(CONNSTRING);
                SqlCommand cmd = new SqlCommand(sqlstr, conn);
                cmd.Parameters.AddRange(paras);
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (SqlException e)
            {

                throw new Exception(e.Message);
            }
        }

        /// <summary>
        ///   注：CommandBehavior.CloseConnection表示关闭SqlDataReader的同时把连接也关掉
        ///返回第一行第一列的方法 
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static object GetExecuteScalar(string sqlstr, params  SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(CONNSTRING))
            {

                try
                {
                    SqlCommand cmd = new SqlCommand(sqlstr, conn);
                    cmd.Parameters.AddRange(paras);
                    conn.Open();
                    return cmd.ExecuteScalar();

                }
                catch (SqlException e)
                {

                    throw new Exception(e.Message);
                }
            }
        }

        /// <summary>
        /// 返回DataTable对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramters"></param>
        /// <returns></returns>
        public static DataTable GetDataSet(string sql, params SqlParameter[] paramters)
        {
            using (SqlConnection conn = new SqlConnection(CONNSTRING))
            {
                try
                {
                    conn.Open();
                    DataSet ds = new DataSet();
                    SqlCommand command = new SqlCommand(sql, conn);
                    command.Parameters.AddRange(paramters);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(ds);
                    return ds.Tables[0];

                }
                catch (SqlException e)
                {

                    throw new Exception(e.Message);
                }
            }
        }
    }
}
